package com.itheima.dao.imp;

import com.itheima.dao.StudentDao;
import com.itheima.domain.Student;
import com.itheima.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;

/**
 * @author 黑马程序员
 * @Company http://www.itheima.com
 */
public class StudentDaoImpl implements StudentDao {

    @Override
    public ArrayList<Student> findAll() {
        Connection connect = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        ArrayList<Student> list = new ArrayList<>();
        try {
            // Class.forName("com.mysql.jdbc.Driver");
            //  connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db14","root","005816");
            connect = JDBCUtils.getConnect();
            ppst = connect.prepareStatement("select * from student");
            rs = ppst .executeQuery();
            while (rs.next()) {
                int id = rs.getInt("sid");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String birthday = rs.getString("birthday");
                list.add(new Student(id, name, age, birthday));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connect, ppst, rs);
        }
        return list;

    }

    @Override
    public Student findById(Integer id) {
        Connection connect = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Student student = null;
        try {
            connect = JDBCUtils.getConnect();
            ppst = connect.prepareStatement("select * from student where sid=?");
            ppst.setInt(1,id);
            rs = ppst.executeQuery();
            if (rs.next()) {
                int sid = rs.getInt("sid");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String birthday = rs.getString("birthday");
                student = new Student(sid, name, age, birthday);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connect, ppst, rs);
        }


        return student;
    }

    @Override
    public int insert(Student stu) {
        int i = 0;
        Connection connect  = null;
        PreparedStatement ppst = null;
        try {
            //Class.forName("com.mysql.jdbc.Driver");
            //connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db14", "root", "005816");
             connect = JDBCUtils.getConnect();
            // statement = connect.createStatement();
            ppst = connect.prepareStatement("insert into student values (null ,?,?,?)");
            ppst.setString(1,stu.getName());
            ppst.setInt(2,stu.getAge());
            ppst.setString(3,stu.getBirthday());
           // i = statement.executeUpdate("INSERT INTO student VALUES (" + stu.getSid() + ",'" + stu.getName() + "'," + stu.getAge() + ",'" + stu.getBirthday() + "')");
            i = ppst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connect ,ppst);
        }

        return i;
    }

    @Override
    public int update(Student stu) {
        int i = 0;
        Connection connection = null;
        Statement statement = null;
        try {
            //Class.forName("com.mysql.jdbc.Driver");
            //connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db14", "root", "005816");
            connection = JDBCUtils.getConnect();
            PreparedStatement ppst = connection.prepareStatement("update student set name =?,age=?,birthday=? where sid=? ");
            // i = statement.executeUpdate("UPDATE student SET NAME='" + stu.getName() + "',age='" + stu.getSid() + "'," + "birthday='" + stu.getBirthday() + "' WHERE sid="+stu.getSid()+";");
            ppst.setString(1,stu.getName());
            ppst.setInt(2,stu.getAge());
            ppst.setString(3,stu.getBirthday());
            ppst.setInt(4,stu.getSid());
            i = ppst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection,statement);

        }
        return i;


    }

    @Override
    public int delete(Integer id) {
        int i = 0;
        Connection connection = null;
        Statement statement = null;
        try {
            //Class.forName("com.mysql.jdbc.Driver");
            //connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db14", "root", "005816");
            connection = JDBCUtils.getConnect();
            PreparedStatement ppst = connection.prepareStatement("delete from student where sid=?");
            ppst.setInt(1,id);
            i = ppst.executeUpdate();
            //statement = connection.createStatement();
           // i = statement.executeUpdate("DELETE FROM student WHERE sid=" + id + ";");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection,statement);
        }
        return i;
    }


}
